package me.chenqiang.tre;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import me.chenqiang.tre.expression.Expression;
import me.chenqiang.tre.expression.ExpressionFactory;
import me.chenqiang.tre.rule.Action;
import me.chenqiang.tre.rule.Rule;
import me.chenqiang.tre.rule.RuleChain;

public class RuleSQliteDatabase {
	/**
	 * 从SQLITE3示例数据库中加载规则链
	 * 
	 * @return
	 * @throws IOException
	 */
	public RuleChain load() throws IOException {
		//将resource中只读的数据库文件写入临时文件
		Path tmpp = Files.createTempFile("rules", ".sqlite3");
		File tmpf = tmpp.toFile();

		try (FileOutputStream fos = new FileOutputStream(tmpf);
				InputStream dis = ClassLoader.getSystemResourceAsStream("rules.sqlite3")) {
			byte[] buffer = new byte[4096];
			int read;
			while ((read = dis.read(buffer)) != -1) {
				fos.write(buffer, 0, read);
			}
		}
		System.out.println(tmpp.toString());

		ExpressionFactory factory = new ExpressionFactory(null);
		String connstr = String.format("jdbc:sqlite:%s", tmpp.toString().replace("\\", "/"));

		ArrayList<Rule> rules = new ArrayList<>();
		try (Connection conn = DriverManager.getConnection(connstr)) {
			Statement ruleStat = conn.createStatement();
			ResultSet ruleRs = ruleStat.executeQuery("select * from rule order by order_id asc, id asc;");
			PreparedStatement exprStat = conn.prepareStatement("select * from expression where rule_id = ?");

			while (ruleRs.next()) {
				int id = ruleRs.getInt("id");
				String name = ruleRs.getString("name");
				boolean actionType = "ACCEPT".equals(ruleRs.getString("action_type"));
				BigDecimal chargeRate = new BigDecimal(ruleRs.getString("charge_rate"));

				exprStat.setInt(1, id);
				ResultSet exprRs = exprStat.executeQuery();
				ArrayList<Expression> exprs = new ArrayList<Expression>();
				while (exprRs.next()) {
					exprs.add(factory.produce(exprRs.getString("property_name"), 
							exprRs.getString("expression_type"),
							exprRs.getString("property_value")));
				}

				rules.add(new Rule(name, exprs, new Action(actionType, chargeRate)));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		tmpf.deleteOnExit();

		return new RuleChain(rules, Action.NORMAL);
	}
	
	/**
	 * 将规则链写入临时文件数据库
	 * @param chain
	 * @return 返回临时文件路径，可用于复制或移动文件
	 * @throws IOException
	 * @throws SQLException
	 */
	public Path dump(RuleChain chain) throws IOException, SQLException {
		String [] ddls = {"CREATE TABLE \"main\".\"rule\" (\r\n"
				+ "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\r\n"
				+ "  \"name\" TEXT NOT NULL,\r\n"
				+ "  \"action_type\" TEXT NOT NULL,\r\n"
				+ "  \"charge_rate\" TEXT NOT NULL DEFAULT '0.00',\r\n"
				+ "  \"order_id\" INTEGER NOT NULL DEFAULT 0\r\n"
				+ ");",
				"CREATE TABLE \"main\".\"expression\" (\r\n"
				+ "  \"id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT,\r\n"
				+ "  \"rule_id\" INTEGER NOT NULL,\r\n"
				+ "  \"property_name\" TEXT NOT NULL,\r\n"
				+ "  \"expression_type\" TEXT NOT NULL,\r\n"
				+ "  \"property_value\" TEXT,\r\n"
				+ "  CONSTRAINT \"fk_rule\" FOREIGN KEY (\"rule_id\") REFERENCES \"rule\" (\"id\") ON DELETE CASCADE ON UPDATE CASCADE\r\n"
				+ ");"};
		
		Path tmpp = Files.createTempFile("rules", ".sqlite3");
		String connstr = String.format("jdbc:sqlite:%s", tmpp.toString().replace("\\", "/"));
		try (Connection conn = DriverManager.getConnection(connstr)) {
			Statement stat = conn.createStatement();
			for(String ddl: ddls) {
				stat.execute(ddl);
			}
			int order = 1;
			PreparedStatement ruleStat = conn.prepareStatement("insert into rule (id, name, action_type, charge_rate, order_id) values (?, ?, ?, ?, ?)");
			PreparedStatement exprStat = conn.prepareStatement("insert into expression (rule_id, property_name, expression_type, property_value) values (?, ?, ?, ?)");
			for(Rule rule : chain.getRules()) {
				ruleStat.setInt(1, order);
				ruleStat.setString(2, rule.getName());
				Action action = rule.getAction();
				ruleStat.setString(3 , action.isTransactionAccepted() ? "ACCEPT": "REJECT");
				ruleStat.setString(4, action.getAdditionalChargeRate() == null ? "0.00": action.getAdditionalChargeRate().toPlainString());
				ruleStat.setInt(5, order);
				ruleStat.execute();
				for(Expression expr: rule.getExpressions()) {
					exprStat.setInt(1, order);
					exprStat.setString(2, expr.getPropertyName());
					exprStat.setString(3, expr.getType());
					exprStat.setString(4, expr.getPropertyValue());
					exprStat.execute();
				}

				order ++;
			}
			
		}
		
		System.out.println(tmpp.toString());
		return tmpp;			
	}
}
